SQL database with R

Author

Tony Duan

1 Connection with database

Code

library(DBI)
library(RSQLite)
library(connections)

1.1 create database file pythonsqlite.db and copy mtcars data and iris data into database

Code

mtcars=cbind(newColName = rownames(mtcars), mtcars)

Code

# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), "my_sql_database")

# view database on IDE
connection_view(con)

Code

dbWriteTable(con, "mtcars", mtcars,overwrite=TRUE)
dbWriteTable(con, "iris", iris,overwrite=TRUE)

1.2 check all table in database

Code

dbListTables(con)

[1] "iris"   "mtcars"

2 SQL

Code

data=dbReadTable(con, "mtcars")
head(data)

         newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

2.1 select

Code

sql ="SELECT * FROM mtcars LIMIT 3"
table=dbGetQuery(con,sql)
table

     newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1     Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3    Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1

2.2 Renaming column

Code

sql="select mpg as new_mpg from mtcars"
table=dbGetQuery(con,sql)
head(table)

2.3 create column

Code

sql="select mpg+1 as new_mpg,mpg from mtcars"
table=dbGetQuery(con,sql)
head(table)

  new_mpg  mpg
1    22.0 21.0
2    22.0 21.0
3    23.8 22.8
4    22.4 21.4
5    19.7 18.7
6    19.1 18.1

2.4 Filter rows

Code

sql="select * from mtcars where hp>100"
table=dbGetQuery(con,sql)
head(table)

         newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
4 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
5           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
6        Duster 360 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4

2.4.1 Filters with AND conditions

Code

sql="select * from mtcars where hp>100 and drat<3"
table=dbGetQuery(con,sql)
head(table)

          newColName  mpg cyl disp  hp drat   wt  qsec vs am gear carb
1            Valiant 18.1   6  225 105 2.76 3.46 20.22  1  0    3    1
2 Cadillac Fleetwood 10.4   8  472 205 2.93 5.25 17.98  0  0    3    4
3   Dodge Challenger 15.5   8  318 150 2.76 3.52 16.87  0  0    3    2

2.4.2 Filters with or conditions

Code

sql="select * from mtcars where hp>100 or drat<3"
table=dbGetQuery(con,sql)
head(table)

         newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
4 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
5           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
6        Duster 360 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4

2.5 Append

2.5.1 append by row

Code

sql="select * from mtcars union all select * from mtcars "
table=dbGetQuery(con,sql)
head(table)

         newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

2.5.2 append by column

2.5.3 Dropping NA values

2.5.4 keep NA values

2.6 group by

2.6.1 average,min,max,sum

Code

sql="select AVG(hp),min(hp),max(hp),sum(hp) from mtcars"
table=dbGetQuery(con,sql)
head(table)

   AVG(hp) min(hp) max(hp) sum(hp)
1 146.6875      52     335    4694

2.6.2 count record and count distinct record

Code

sql="select vs, count(*),count(distinct cyl) from mtcars group by vs"
table=dbGetQuery(con,sql)
head(table)

  vs count(*) count(distinct cyl)
1  0       18                   3
2  1       14                   2

2.7 order rows

Code

sql="select * from mtcars order by mpg"
table=dbGetQuery(con,sql)
head(table)

           newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1  Cadillac Fleetwood 10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
2 Lincoln Continental 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4
3          Camaro Z28 13.3   8  350 245 3.73 3.840 15.41  0  0    3    4
4          Duster 360 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4
5   Chrysler Imperial 14.7   8  440 230 3.23 5.345 17.42  0  0    3    4
6       Maserati Bora 15.0   8  301 335 3.54 3.570 14.60  0  1    5    8

2.7.1 Sort in descending order

Code

sql="select * from mtcars order by mpg desc"
table=dbGetQuery(con,sql)
head(table)

      newColName  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1 Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
2       Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
3    Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
4   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
5      Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
6  Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2

2.7.2 Arrange by multiple variables

Code

sql="select * from mtcars order by mpg ,cyl"
table=dbGetQuery(con,sql)
head(table)

           newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1  Cadillac Fleetwood 10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
2 Lincoln Continental 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4
3          Camaro Z28 13.3   8  350 245 3.73 3.840 15.41  0  0    3    4
4          Duster 360 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4
5   Chrysler Imperial 14.7   8  440 230 3.23 5.345 17.42  0  0    3    4
6       Maserati Bora 15.0   8  301 335 3.54 3.570 14.60  0  1    5    8

2.8 join

2.8.1 inner_join

Code

sql="select a.newColName,a.mpg,b.mpg as new_mpg from mtcars a left join mtcars b on a.newColName=b.newColName"

table=dbGetQuery(con,sql)
head(table)

         newColName  mpg new_mpg
1         Mazda RX4 21.0    21.0
2     Mazda RX4 Wag 21.0    21.0
3        Datsun 710 22.8    22.8
4    Hornet 4 Drive 21.4    21.4
5 Hornet Sportabout 18.7    18.7
6           Valiant 18.1    18.1

2.8.2 full join

2.8.3 left join

2.8.4 anti join

2.9 Reshape tables

2.9.1 Gather data long(wide to long)

2.9.2 Spread data wide (long to wide)

2.10 string

2.10.1 upper case

2.10.2 lower case

2.10.3 match

2.10.4 concatenation

2.10.5 replace

2.10.6 extract

2.11 date

2.12 create table into database

Code

sql="create table if not exists new_mtcars as select * from mtcars order by mpg ,cyl"
dbSendQuery(con,sql)

<SQLiteResult>
  SQL  create table if not exists new_mtcars as select * from mtcars order by mpg ,cyl
  ROWS Fetched: 0 [complete]
       Changed: 0

Code

dbListTables(con)

[1] "iris"       "mtcars"     "new_mtcars"

2.13 delete table in database

Code

sql="drop table if  exists  new_mtcars"
dbSendQuery(con,sql)

<SQLiteResult>
  SQL  drop table if  exists  new_mtcars
  ROWS Fetched: 0 [complete]
       Changed: 0

Code

dbListTables(con)

[1] "iris"   "mtcars"

2.14 edit table in database

3 Using SQL with R dataframe

Code

library(sqldf)

Code

data=sqldf("select * from mtcars LIMIT 3;")
data

     newColName  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1     Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3    Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1

1 Connection with database

1.1 create database file pythonsqlite.db and copy mtcars data and iris data into database

1.2 check all table in database

2 SQL

2.1 select

2.2 Renaming column

2.3 create column

2.4 Filter rows

2.4.1 Filters with AND conditions

2.4.2 Filters with or conditions

2.5 Append

2.5.1 append by row

2.5.2 append by column

2.5.3 Dropping NA values

2.5.4 keep NA values

2.6 group by

2.6.1 average,min,max,sum

2.6.2 count record and count distinct record

2.7 order rows

2.7.1 Sort in descending order

2.7.2 Arrange by multiple variables

2.8 join

2.8.1 inner_join

2.8.2 full join

2.8.3 left join

2.8.4 anti join

2.9 Reshape tables

2.9.1 Gather data long(wide to long)

2.9.2 Spread data wide (long to wide)

2.10 string

2.10.1 upper case

2.10.2 lower case

2.10.3 match

2.10.4 concatenation

2.10.5 replace

2.10.6 extract

2.11 date

2.12 create table into database

2.13 delete table in database

2.14 edit table in database

3 Using SQL with R dataframe

4 reference: